﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Spread.Common.Features.Sample;
using System.IO;
using GrapeCity.AnnualFinancialReport.Properties;
using FarPoint.Win.Spread;
using FarPoint.Win;
using FarPoint.Win.Spread.CellType;
using FarPoint.Win.Spread.Model;
using FarPoint.Win.Spread.Chart;

namespace GrapeCity.AnnualFinancialReport
{
    public partial class AnnualFinancialReport : BaseSampleControl
    {
        public AnnualFinancialReport()
        {
            InitializeComponent();
            SetupComponents();
            InitFinancialDataInput(_svFinancialDataInput);
            InitKeyMetricsSettings(_svKeyMetricSettings);
            InitCalculations(_svCalculations);
            InitFinancialReport(_svFinancialReport);
        }
        private void SetupComponents()
        {
            //Load FpSpread mappings
            using (MemoryStream stream = new MemoryStream(Resources.ExcelCompatibility))
            {
                fpSpread.LoadXmlInputMapFile(stream);
            }

            //Attach namebox and formulabox to spread
            this.nameBox.Attach(this.fpSpread);
            this.formulaTextBox1.Attach(this.fpSpread);
            
            //Perform actions on MenuItem clicks
            _miConditionalFormats.Click += (s, e) =>
            {
                ConditionalFormattingUtils.ShowConditionalFormattingManagerForm(this.ParentForm, fpSpread);
            };
            _miNameManager.Click += (s, e) => { fpSpread.ShowListCustomNameForm(this.ParentForm); };
        }
        private void InitFinancialDataInput(SheetView dataInput)
        {
            dataInput.HorizontalGridLine = new GridLine(GridLineType.None);
            dataInput.VerticalGridLine = new GridLine(GridLineType.None);
            dataInput.SetColumnWidth(0, 15);
            dataInput.SetColumnWidth(1, 150);
            dataInput.SetMultipleColumnWidths(2, 7, 121);
            dataInput.SetColumnWidth(9, 15);
            dataInput.SetRowHeight(0, 11);
            dataInput.SetRowHeight(1, 51);
            dataInput.SetRowHeight(2, 24);
            dataInput.SetMultipleRowHeights(3, 2, 34);
            dataInput.SetMultipleRowHeights(5, 25, 26);
            dataInput.Cells[1, 1].Value = "INPUT YOUR FINANCIAL DATA";
            dataInput.Cells[1, 1].ForeColor = Color.FromArgb(255, 38, 94, 53);
            dataInput.Cells[1, 1].Font = new Font("Trebuchet MS", 24);
            dataInput.Cells[2, 1].Value = "YOU CAN DEFINE UP TO 25 KEY METRICS FOR 7 YEARS";
            dataInput.Cells[2, 1].ForeColor = Color.FromArgb(255, 89, 89, 89);
            dataInput.Cells[2, 1].Font = new Font("Ariel", 14);
            dataInput.SetArray(4, 1, new object[,] { { "METRIC NAME",           2010,        2011,      2012,      2013,      2014,      2015,      2016 },
                                              { "REVENUES",            125000,   134137.45, 142728.38, 150687.46, 165044.56, 180026.63, 180583.88 },
                                              { "OPERATING EXPENSES",   65000,    70962.31,  75924.86,  78901.27,  81674.37,  80883.33,  94419.45 },
                                              { "OPERATING PROFIT",  60000.00,    64207.30,  68857.69,  75643.25,  76755.26,  77317.83,  73425.99 },
                                              { "DEPRECIATION",       4500.00,     4517.77,   4656.92,   4974.21,   5024.11,   5068.42,   5546.88 },
                                              { "INTEREST",           2500.00,     2745.82,   2893.11,   3136.12,   3148.53,   3338.30,   3789.47 },
                                              { "NET PROFIT",        54000.00,    54761.08,  55860.81,  59747.95,  61483.59,  66272.10,  67474.85 },
                                              { "TAX",               22000.00,    23920.54,  25576.74,  27498.86,  28335.67,  29424.53,  31408.25 },
                                              { "PROFIT AFTER TAX",  32000.00,    34943.49,  38418.53,  39895.05,  40607.73,  42438.20,  50247.68 },
                                              { "METRIC 1",             12.80,       12.81,     13.78,     14.29,     15.57,     16.78,     19.96 },
                                              { "METRIC 2",             18.20,       18.59,     19.22,     20.17,     20.48,     21.84,     26.01 },
                                              { "METRIC 3",             19.10,       20.55,     21.87,     23.19,     24.67,     26.39,     31.08 },
                                              { "METRIC 4",             12.10,       12.21,     12.59,     13.70,     13.76,     14.59,     14.92 },
                                              { "METRIC 5",              0.75,       0.79,       0.85,      0.89,      0.91,      1.00,      1.03 },
                                              { "METRIC 6",              0.23,       0.25,       0.27,      0.28,      0.29,      0.30,      0.34 } });
            dataInput.Cells[4, 1, 4, 8].ForeColor = Color.White;
            dataInput.Cells[4, 1, 4, 8].BackColor = Color.FromArgb(255, 38, 94, 53);
            dataInput.Cells[4, 1, 4, 8].Font = new Font("Ariel", 11);
            dataInput.FrozenRowCount = 5;
            ComplexBorderSide sideThin = new ComplexBorderSide(ComplexBorderSideStyle.ThinLine, System.Drawing.Color.Black);
            dataInput.Rows[4].Border = new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideThin);
            FormulaConditionalFormattingRule formatTable = new FormulaConditionalFormattingRule("MOD(ROW(),2)=0");
            formatTable.BackColor = Color.FromArgb(255, 242, 242, 242);
            ComplexBorderSide sideDarkGray = new ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217));
            formatTable.Border = new ComplexBorder(ComplexBorderSide.Empty, sideDarkGray, ComplexBorderSide.Empty, sideDarkGray);
            dataInput.SetConditionalFormatting(new CellRange[] { new CellRange(5, 1, 25, 8) }, new IConditionalFormattingRule[] { formatTable });
            CurrencyCellType currencyCell = new CurrencyCellType();
            currencyCell.ShowSeparator = true;
            dataInput.Cells[5, 2, 29, 8].CellType = currencyCell;
            dataInput.DefaultStyle.Locked = true;
            dataInput.Cells[5, 1, 29, 8].Locked = false;
            dataInput.Protect = true;
        }

        private void InitKeyMetricsSettings(SheetView keyMetricSettings)
        {
            keyMetricSettings.HorizontalGridLine = new GridLine(GridLineType.None);
            keyMetricSettings.VerticalGridLine = new GridLine(GridLineType.None);
            keyMetricSettings.SetColumnWidth(-1, 64);
            keyMetricSettings.SetColumnWidth(0, 15);
            keyMetricSettings.SetColumnWidth(1, 30);
            keyMetricSettings.SetColumnWidth(2, 168);
            keyMetricSettings.SetColumnWidth(3, 28);
            keyMetricSettings.SetMultipleColumnWidths(4, 2, 127);
            keyMetricSettings.SetRowHeight(-1, 26);
            keyMetricSettings.SetRowHeight(0, 11);
            keyMetricSettings.SetRowHeight(1, 51);
            keyMetricSettings.SetRowHeight(2, 34);
            keyMetricSettings.SetRowHeight(3, 31);
            keyMetricSettings.Cells[1, 1].Value = "DEFINE KEY METRICS HERE";
            keyMetricSettings.Cells[1, 1].ForeColor = Color.FromArgb(255, 38, 94, 53);
            keyMetricSettings.Cells[1, 1].Font = new Font("Trebuchet MS", 24);
            keyMetricSettings.Cells[2, 1].Value = "SELECT UP TO 5 KEY METRICS TO SHOW AT THE TOP OF THE REPORT";
            keyMetricSettings.Cells[2, 1].ForeColor = Color.FromArgb(255, 89, 89, 89);
            keyMetricSettings.Cells[2, 1].Font = new Font("Ariel", 14);
            keyMetricSettings.SetArray(4, 1, new object[,] { { 1, "REVENUES"},
                                                      { 2, "NET PROFIT" },
                                                      { 3, "INTEREST" },
                                                      { 4, "DEPRECIATION"},
                                                      { 5, "OPERATING PROFIT"}});
            keyMetricSettings.Cells[4, 1, 8, 1].Font = new Font("Trebuchet MS", 11);
            keyMetricSettings.Cells[4, 2, 8, 2].Font = new Font("Trebuchet MS", 10);
            keyMetricSettings.AddCustomName("lstMetrics", "OFFSET('Financial Data Input'!$B$6:$B$30,0,0,COUNTA('Financial Data Input'!$B$6:$B$30),1)", 0, 0);
            GrapeCity.Spreadsheet.DataValidation lstMetrics = keyMetricSettings.Validations.Add(new GrapeCity.CalcEngine.RangeReference(4, 2, 8, 2), GrapeCity.Spreadsheet.DataValidationType.List, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=lstMetrics");
            lstMetrics.ErrorMessage = "A metric from your financial data needs to be entered for Key Metrics to work correctly. Click Cancel and enter a different metric or add the metric to the Financial Data Input sheet. ";
            lstMetrics.ErrorTitle = "Whoops!";
            FormulaConditionalFormattingRule formatTableL = new FormulaConditionalFormattingRule("MOD(ROW(),2)=0");
            formatTableL.BackColor = Color.FromArgb(255, 242, 242, 242);
            formatTableL.Border = new ComplexBorder(new ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166)), new ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)), ComplexBorderSide.Empty, new ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)));
            keyMetricSettings.SetConditionalFormatting(new CellRange[] { new CellRange(4, 1, 5, 1) }, new IConditionalFormattingRule[] { formatTableL });
            FormulaConditionalFormattingRule formatTableR = new FormulaConditionalFormattingRule("MOD(ROW(),2)=0");
            formatTableR.BackColor = Color.FromArgb(255, 242, 242, 242);
            formatTableR.Border = new ComplexBorder(ComplexBorderSide.Empty, new ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)), new ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166)), new ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)));
            keyMetricSettings.SetConditionalFormatting(new CellRange[] { new CellRange(4, 2, 5, 1) }, new IConditionalFormattingRule[] { formatTableR });
            keyMetricSettings.SetOutlineBorder(new CellRange(4, 1, 5, 2), new ComplexBorder(new ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))));
            keyMetricSettings.Cells[4, 4, 8, 4].Font = new Font("Ariel", 11);
            keyMetricSettings.Cells[4, 4, 8, 4].ForeColor = Color.FromArgb(255, 128, 128, 128);
            keyMetricSettings.Cells[4, 4, 8, 4].Formula = "IF(ISBLANK(C5),\"← Please select a value from drop-down\",IF(COUNTIF($C$5:C5,C5)>1,\"You have selected \"&C5&\" more than once.\",\"\"))";
            keyMetricSettings.DefaultStyle.Locked = true;
            keyMetricSettings.Cells[4, 2, 8, 8].Locked = false;
            keyMetricSettings.Protect = true;
            keyMetricSettings.SetActiveCell(4, 2);
        }
        private void InitCalculations(SheetView calculations)
        {
            calculations.SetColumnWidth(-1, 64);
            calculations.SetColumnWidth(1, 230);
            calculations.SetRowHeight(0, 46);
            calculations.SetMultipleRowHeights(2, 10, 26);
            calculations.SetRowHeight(12, 21);
            calculations.SetMultipleRowHeights(13, 26, 26);
            calculations.DefaultStyle.Font = new Font("Trebuchet MS", 10);
            calculations.DefaultStyle.ForeColor = Color.FromArgb(255, 89, 89, 89);
            calculations.DefaultStyle.VerticalAlignment = CellVerticalAlignment.Center;
            calculations.Cells[0, 0].Value = "This worksheet is used for the Financial Report calculations and should remain hidden.";
            calculations.Cells[0, 0].ForeColor = Color.FromArgb(255, 57, 140, 80);
            calculations.Cells[0, 0].Font = new Font("Ariel", 11);
            calculations.Cells[1, 3].Value = "Position";
            calculations.AddCustomName("lstYears", "OFFSET('Financial Data Input'!$B$5:$I$5,0,1,1,COUNTA('Financial Data Input'!$B$5:$I$5)-1)", 0, 0);
            calculations.AddCustomName("SelectedYear", "'Financial Report'!$K$2", 0, 0);
            calculations.Cells[2, 1].Value = "This year";
            calculations.Cells[2, 2].Formula = "SelectedYear";
            calculations.Cells[2, 3].Formula = "MATCH(C3,lstYears,0)+1";
            calculations.Cells[3, 1].Value = "Previous Year";
            calculations.Cells[3, 2].Formula = "C3-1";
            calculations.Cells[3, 3].Formula = "MATCH(C4,lstYears,0)+1";
            calculations.Cells[5, 1].Value = "Position";
            calculations.Cells[5, 2, 5, 6].Formula = "MATCH(C7,lstYears,0)+1";
            calculations.Cells[5, 8].Formula = "COUNT(C6:G6)";
            calculations.AddCustomName("Years", "Calculations!$I$6", 0, 0);
            calculations.SetOutlineBorder(new CellRange(5, 1, 1, 6), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, new ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))));
            calculations.Cells[6, 1].Value = "Key Metrics";
            calculations.Cells[6, 2, 6, 5].Formula = "D7-1";
            calculations.Cells[6, 6].Formula = "C3";
            calculations.Cells[6, 1, 6, 6].Font = new Font("Trebuchet MS", 14);
            calculations.SetOutlineBorder(new CellRange(6, 1, 1, 6), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, new ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))));
            calculations.Cells[7, 0, 11, 0].Formula = "MATCH(B8,'Financial Data Input'!$B$6:$B$30,0)";
            calculations.Cells[7, 1, 11, 1].Formula = "IF('Key Metric Settings'!C5=\"\",\"\",'Key Metric Settings'!C5)";
            calculations.Cells[7, 2, 11, 6].Formula = "IFERROR(INDEX('Financial Data Input'!$B$6:$I$30,$A8,C$6),NA())";
            calculations.Cells[7, 7, 11, 7].Formula = "IFERROR(G8/F8-1,\"\")";
            PercentCellType percentCell = new PercentCellType();
            percentCell.DecimalPlaces = 0;
            calculations.Cells[7, 7, 11, 7].CellType = percentCell;
            calculations.SetOutlineBorder(new CellRange(12, 1, 1, 6), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, new ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))));
            calculations.Cells[13, 1].Value = "All Metrics (works up to 25 metrics)";
            calculations.Cells[13, 1].Font = new Font("Trebuchet MS", 14);
            calculations.SetOutlineBorder(new CellRange(13, 1, 1, 6), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, new ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))));
            calculations.Cells[14, 0, 38, 0].Formula = "ROWS($B$15:B15)";
            calculations.Cells[14, 1, 38, 1].Formula = "IF('Financial Data Input'!B6=0,\"\",'Financial Data Input'!B6)";
            calculations.Cells[14, 2, 38, 6].Formula = "IF($B15=\"\",NA(),IFERROR(INDEX('Financial Data Input'!$B$6:$I$30,$A15,C$6),NA()))";
        }
        private void InitFinancialReport(SheetView report)
        {
            int r, c;
            report.HorizontalGridLine = new GridLine(GridLineType.None);
            report.VerticalGridLine = new GridLine(GridLineType.None);
            report.SetColumnWidth(-1, 64);
            report.SetColumnWidth(0, 14);
            for (c = 1; c < 9; c += 2)
            {
                report.SetColumnWidth(c, 185);
                report.SetColumnWidth(c + 1, 19);
            }
            for (c = 9; c < 13; c += 2)
            {
                report.SetColumnWidth(c, 86);
                report.SetColumnWidth(c + 1, 13);
            }
            report.SetColumnWidth(13, 125);
            report.SetRowHeight(-1, 25);
            report.SetRowHeight(0, 11);
            report.SetRowHeight(1, 51);
            report.SetRowHeight(2, 85);
            report.SetRowHeight(3, 9);
            report.SetRowHeight(4, 32);
            report.SetRowHeight(5, 25);
            report.SetRowHeight(6, 30);
            report.SetRowHeight(7, 56);
            report.SetRowHeight(12, 32);
            report.Cells[1, 1].Font = new Font("Trebuchet MS", 24);
            report.Cells[1, 1].ForeColor = Color.FromArgb(255, 38, 94, 53);
            report.Cells[1, 1].Value = "ANNUAL FINANCIAL REPORT";
            ComplexBorderSide sideMediumGray = new ComplexBorderSide(ComplexBorderSideStyle.MediumLine, System.Drawing.Color.FromArgb(255, 166, 166, 166));
            report.Cells[1, 10].Border = new ComplexBorder(ComplexBorderSide.Empty, sideMediumGray, ComplexBorderSide.Empty, sideMediumGray);
            report.Cells[1, 10].ColumnSpan = 2;
            report.Cells[1, 10].Font = new Font("Trebuchet MS", 20);
            report.Cells[1, 10].HorizontalAlignment = CellHorizontalAlignment.Center;
            report.Cells[1, 10].Value = 2016;
            report.Cells[1, 13].BackColor = Color.FromArgb(255, 38, 94, 53);
            ComplexBorderSide sideGreen = new ComplexBorderSide(Color.FromArgb(255, 57, 140, 80));
            report.Cells[1, 13].Border = new ComplexBorder(sideGreen);
            report.Cells[1, 13].Font = new Font("Ariel", 10, FontStyle.Bold);
            report.Cells[1, 13].ForeColor = Color.White;
            report.Cells[1, 13].HorizontalAlignment = CellHorizontalAlignment.Center;
            report.Cells[1, 13].VerticalAlignment = CellVerticalAlignment.Center;
            report.Cells[1, 13].Value = "Select  report year in Cell K2";
            report.Cells[2, 13].BackColor = Color.FromArgb(255, 38, 94, 53);
            report.Cells[2, 13].Border = new ComplexBorder(sideGreen);
            report.Cells[2, 13].Font = new Font("Ariel", 10, FontStyle.Bold);
            report.Cells[2, 13].ForeColor = Color.White;
            report.Cells[2, 13].HorizontalAlignment = CellHorizontalAlignment.Center;
            report.Cells[2, 13].VerticalAlignment = CellVerticalAlignment.Center;
            report.Cells[2, 13].Value = "To edit data, select the Financial Data Input sheet";
            TextCellType textCell = new TextCellType();
            textCell.WordWrap = true;
            report.Cells[1, 13, 2, 13].CellType = textCell;
            GrapeCity.Spreadsheet.DataValidation lstYears = report.Validations.Add(new GrapeCity.CalcEngine.RangeReference(1, 10, 1, 10), GrapeCity.Spreadsheet.DataValidationType.List, GrapeCity.Spreadsheet.DataValidationErrorStyle.Stop, GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=lstYears");
            lstYears.ErrorMessage = "A year included in your financial data needs to be entered for this report to work correctly. Click Cancel and enter a different year or add information for the year on the Financial Data Input sheet.";
            lstYears.ErrorTitle = "Whoops!";
            lstYears.ShowError = true;
            report.Cells[2, 1].ForeColor = Color.FromArgb(255, 89, 89, 89);
            report.Cells[2, 1].Font = new Font("Ariel", 18);
            report.Cells[2, 1].Value = "YOUR COMPANY NAME";
            report.SetOutlineBorder(new CellRange(3, 1, 1, 11), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray));
            report.Cells[4, 1].ForeColor = Color.FromArgb(255, 89, 89, 89);
            report.Cells[4, 1].Font = new Font("Trebuchet MS", 14);
            report.Cells[4, 1].Value = "KEY METRICS";
            report.SetOutlineBorder(new CellRange(4, 1, 1, 11), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray));
            CurrencyCellType currencyCell = new CurrencyCellType();
            currencyCell.DecimalPlaces = 0;
            currencyCell.ShowSeparator = true;
            PercentCellType percentCell = new PercentCellType();
            percentCell.DecimalPlaces = 0;
            ComplexBorderSide sideMediumGreen = new ComplexBorderSide(ComplexBorderSideStyle.MediumLine, System.Drawing.Color.FromArgb(255, 38, 94, 53));
            ComplexBorderSide sideDashed = new ComplexBorderSide(ComplexBorderSideStyle.Dashed, System.Drawing.Color.FromArgb(255, 89, 89, 89));
            for (c = 1; c < 10; c += 2)
            {
                report.Cells[6, c].BackColor = Color.FromArgb(255, 38, 94, 53);
                report.Cells[6, c].Border = new ComplexBorder(sideMediumGreen);
                report.Cells[6, c].Font = new Font("Ariel", 9, FontStyle.Bold);
                report.Cells[6, c].ForeColor = Color.White;
                report.Cells[6, c].Formula = $"Calculations!B{8 + (c - 1) / 2}";
                report.Cells[6, c].HorizontalAlignment = CellHorizontalAlignment.Center;
                report.Cells[7, c].Border = new ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, sideDashed);
                report.Cells[7, c].CellType = currencyCell;
                report.Cells[7, c].Font = new Font("Ariel", 20);
                report.Cells[7, c].ForeColor = Color.FromArgb(255, 89, 89, 89);
                report.Cells[7, c].Formula = $"IFERROR(Calculations!G{8 + (c - 1) / 2},\"\")";
                report.Cells[7, c].HorizontalAlignment = CellHorizontalAlignment.Center;
                report.Cells[8, c].Border = new ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, ComplexBorderSide.Empty);
                report.Cells[8, c].CellType = percentCell;
                report.Cells[8, c].Font = new Font("Ariel", 12);
                report.Cells[8, c].ForeColor = Color.FromArgb(255, 89, 89, 89);
                report.Cells[8, c].Formula = $"Calculations!H{8 + (c - 1) / 2}";
                report.Cells[8, c].HorizontalAlignment = CellHorizontalAlignment.Left;
                report.Cells[8, c].TextIndent = 2;
                report.Cells[9, c].Border = new ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, ComplexBorderSide.Empty);
                if (c < 9)
                    report.Cells[10, c].Border = new ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, sideMediumGreen);
                else
                    report.SetOutlineBorder(new CellRange(10, c, 1, 3), new ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, sideMediumGreen));
            }
            IconSetConditionalFormattingRule iconRule = new IconSetConditionalFormattingRule(ConditionalFormattingIconSetStyle.ThreeColoredArrows);
            iconRule.IconRuleSet.SetItem(0, new FarPoint.Win.Spread.ConditionalFormattingIconValue(0.005D, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, false, new FarPoint.Win.Spread.ConditionalFormattingIconSetIndex(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeColoredArrows, 2)));
            iconRule.IconRuleSet.SetItem(1, new FarPoint.Win.Spread.ConditionalFormattingIconValue(-0.005D, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, true, new FarPoint.Win.Spread.ConditionalFormattingIconSetIndex(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeColoredArrows, 1)));
            iconRule.IconRuleSet.SetItem(2, new FarPoint.Win.Spread.ConditionalFormattingIconValue(0D, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, true, new FarPoint.Win.Spread.ConditionalFormattingIconSetIndex(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeColoredArrows, 0)));
            List<CellRange> iconCells = new List<CellRange>();
            for (c = 1; c < 10; c += 2)
                iconCells.Add(new CellRange(8, c, 1, 1));
            for (r = 15; r < 40; r++)
                iconCells.Add(new CellRange(r, 7, 1, 1));
            report.SetConditionalFormatting(iconCells.ToArray(), new IConditionalFormattingRule[] { iconRule });
            ExcelSparklineSetting sparklineSettingMetrics = new ExcelSparklineSetting();
            sparklineSettingMetrics.ShowMarkers = true;
            sparklineSettingMetrics.MarkersColor = Color.FromArgb(255, 38, 94, 53);
            sparklineSettingMetrics.SeriesColor = System.Drawing.Color.FromArgb(255, 166, 166, 166);
            List<SheetCellRange> sparklineMetricsDataCells = new List<SheetCellRange>();
            for (r = 7; r < 12; r++)
                sparklineMetricsDataCells.Add(new SheetCellRange("Calculations", r, 2, 1, 5));
            List<CellRange> sparklineMetricsCells = new List<CellRange>();
            for (c = 1; c < 10; c += 2)
                sparklineMetricsCells.Add(new CellRange(9, c, 1, 1));
            ExcelSparklineGroup sparklineGroupKeyMetrics = report.AddSparkline(sparklineMetricsDataCells.ToArray(), sparklineMetricsCells.ToArray(), SparklineType.Line, sparklineSettingMetrics);
            report.Cells[6, 9].ColumnSpan = 3;
            report.Cells[7, 9].ColumnSpan = 3;
            report.Cells[8, 9].ColumnSpan = 3;
            report.Cells[9, 9].ColumnSpan = 3;
            report.SetOutlineBorder(new CellRange(11, 1, 1, 11), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray));
            report.Cells[12, 1].Value = "ALL METRICS";
            report.Cells[12, 1].Font = new Font("Trebuchet MS", 14);
            report.Cells[12, 1].ForeColor = Color.FromArgb(255, 89, 89, 89);
            report.SetOutlineBorder(new CellRange(12, 1, 1, 11), new ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray));
            report.Cells[14, 1, 14, 11].BackColor = Color.FromArgb(255, 38, 94, 53);
            report.Cells[14, 1, 14, 11].Border = new ComplexBorder(sideMediumGreen);
            report.Cells[14, 1, 14, 11].Font = new Font("Ariel", 11, FontStyle.Bold);
            report.Cells[14, 1, 14, 11].ForeColor = Color.White;
            report.Cells[14, 1, 14, 11].TextIndent = 8;
            report.Cells[14, 1].Value = "METRIC";
            report.Cells[14, 3].Formula = "\"REPORT YEAR (\"&SelectedYear&\")\"";
            report.Cells[14, 5].Formula = "\"PREVIOUS YEAR (\"&SelectedYear-1&\")\"";
            report.Cells[14, 7].HorizontalAlignment = CellHorizontalAlignment.Center;
            report.Cells[14, 7].Value = "% CHANGE";
            for (r = 14; r < 40; r++)
                report.Cells[r, 8, r, 8].ColumnSpan = 4;
            report.Cells[14, 8].Formula = "CONCATENATE(Years,\" YEAR TREND\")";
            FormulaConditionalFormattingRule formatTable = new FormulaConditionalFormattingRule("MOD(ROW(),2)=0");
            formatTable.BackColor = Color.FromArgb(255, 242, 242, 242);
            ComplexBorderSide sideLightGray = new ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217));
            formatTable.Border = new ComplexBorder(ComplexBorderSide.Empty, sideLightGray, ComplexBorderSide.Empty, sideLightGray);
            report.SetConditionalFormatting(new CellRange[] { new CellRange(15, 1, 25, 11) }, new IConditionalFormattingRule[] { formatTable });
            report.Cells[15, 1, 39, 1].Formula = "Calculations!B15";
            report.Cells[15, 3, 39, 3].CellType = currencyCell;
            report.Cells[15, 3, 39, 3].Formula = "IF($B16=\"\",\"\",Calculations!G15)";
            report.Cells[15, 5, 39, 5].CellType = currencyCell;
            report.Cells[15, 5, 39, 5].Formula = "IF($B16=\"\",\"\",Calculations!F15)";
            report.Cells[15, 7, 39, 7].CellType = percentCell;
            report.Cells[15, 7, 39, 7].Formula = "IFERROR($D16/$F16-1,\"\")";
            report.Cells[15, 7, 39, 7].HorizontalAlignment = CellHorizontalAlignment.Center;
            report.Cells[15, 1, 39, 9].Font = new Font("Trebuchet MS", 10);
            report.Cells[15, 1, 39, 9].ForeColor = Color.FromArgb(255, 89, 89, 89);
            report.Cells[15, 1, 39, 9].TextIndent = 11;
            ExcelSparklineSetting sparklineSettingTrends = new ExcelSparklineSetting();
            sparklineSettingTrends.ShowMarkers = true;
            sparklineSettingTrends.MarkersColor = Color.FromArgb(255, 38, 94, 53);
            sparklineSettingTrends.SeriesColor = System.Drawing.Color.FromArgb(255, 166, 166, 166);
            List<SheetCellRange> sparklineAllMetricsDataCells = new List<SheetCellRange>();
            for (r = 14; r < 39; r++)
                sparklineAllMetricsDataCells.Add(new SheetCellRange("Calculations", r, 2, 1, 5));
            List<CellRange> trendRanges = new List<CellRange>();
            for (r = 15; r < 40; r++)
                trendRanges.Add(new CellRange(r, 8, 1, 1));
            ExcelSparklineGroup sparklineAllMetrics = report.AddSparkline(sparklineAllMetricsDataCells.ToArray(), trendRanges.ToArray(), SparklineType.Line, sparklineSettingTrends);
            report.DefaultStyle.Locked = true;
            report.Cells[1, 10].Locked = false;
            report.Protect = true;
            report.SetActiveCell(1, 10);
        }
    }
}


//
//AnnualFinancialReport.Designer.cs
//


namespace GrapeCity.AnnualFinancialReport
{
    partial class AnnualFinancialReport
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            FarPoint.Win.Spread.FlatScrollBarRenderer flatScrollBarRenderer1 = new FarPoint.Win.Spread.FlatScrollBarRenderer();
            FarPoint.Win.Spread.FlatScrollBarRenderer flatScrollBarRenderer2 = new FarPoint.Win.Spread.FlatScrollBarRenderer();
            this.fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this._svFinancialReport = new FarPoint.Win.Spread.SheetView();
            this._svFinancialDataInput = new FarPoint.Win.Spread.SheetView();
            this._svKeyMetricSettings = new FarPoint.Win.Spread.SheetView();
            this._svCalculations = new FarPoint.Win.Spread.SheetView();
            this.menuStrip1 = new System.Windows.Forms.MenuStrip();
            this.viewToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();
            this._miConditionalFormats = new System.Windows.Forms.ToolStripMenuItem();
            this._miNameManager = new System.Windows.Forms.ToolStripMenuItem();
            this.nameBox = new FarPoint.Win.Spread.NameBox();
            this.formulaTextBox1 = new FarPoint.Win.Spread.FormulaTextBox();
            this.pnlMain.SuspendLayout();
            this.pnlControls.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._svFinancialReport)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._svFinancialDataInput)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._svKeyMetricSettings)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this._svCalculations)).BeginInit();
            this.menuStrip1.SuspendLayout();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            // 
            // pnlControls
            // 
            this.pnlControls.Controls.Add(this.formulaTextBox1);
            this.pnlControls.Controls.Add(this.nameBox);
            this.pnlControls.Controls.Add(this.menuStrip1);
            this.pnlControls.Padding = new System.Windows.Forms.Padding(5);
            // 
            // fpSpread
            // 
            this.fpSpread.AccessibleDescription = "fpSpread, Calculations, Row 0, Column 0";
            this.fpSpread.AllowCellOverflow = true;
            this.fpSpread.AllowDragDrop = true;
            this.fpSpread.AllowDragFill = true;
            this.fpSpread.AllowEditOverflow = true;
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.DragFillDataOnly = true;
            this.fpSpread.HorizontalScrollBar.Buttons = new FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton");
            this.fpSpread.HorizontalScrollBar.Name = "";
            flatScrollBarRenderer1.ArrowColor = System.Drawing.Color.FromArgb(((int)(((byte)(121)))), ((int)(((byte)(121)))), ((int)(((byte)(121)))));
            flatScrollBarRenderer1.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(255)))));
            flatScrollBarRenderer1.BorderActiveColor = System.Drawing.Color.FromArgb(((int)(((byte)(171)))), ((int)(((byte)(171)))), ((int)(((byte)(171)))));
            flatScrollBarRenderer1.TrackBarBackColor = System.Drawing.Color.FromArgb(((int)(((byte)(219)))), ((int)(((byte)(219)))), ((int)(((byte)(219)))));
            this.fpSpread.HorizontalScrollBar.Renderer = flatScrollBarRenderer1;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.RangeDragFillMode = FarPoint.Win.Spread.DragFillMode.Series;
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this._svFinancialReport,
            this._svFinancialDataInput,
            this._svKeyMetricSettings,
            this._svCalculations});
            this.fpSpread.Size = new System.Drawing.Size(477, 487);
            this.fpSpread.TabIndex = 0;
            this.fpSpread.VerticalScrollBar.Buttons = new FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton");
            this.fpSpread.VerticalScrollBar.Name = "";
            flatScrollBarRenderer2.ArrowColor = System.Drawing.Color.FromArgb(((int)(((byte)(121)))), ((int)(((byte)(121)))), ((int)(((byte)(121)))));
            flatScrollBarRenderer2.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(255)))));
            flatScrollBarRenderer2.BorderActiveColor = System.Drawing.Color.FromArgb(((int)(((byte)(171)))), ((int)(((byte)(171)))), ((int)(((byte)(171)))));
            flatScrollBarRenderer2.TrackBarBackColor = System.Drawing.Color.FromArgb(((int)(((byte)(219)))), ((int)(((byte)(219)))), ((int)(((byte)(219)))));
            this.fpSpread.VerticalScrollBar.Renderer = flatScrollBarRenderer2;
            // 
            // _svFinancialReport
            // 
            this._svFinancialReport.Reset();
            this._svFinancialReport.SheetName = "Financial Report";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._svFinancialReport.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._svFinancialReport.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialReport.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialReport.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialReport.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialReport.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialReport.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialReport.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialReport.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialReport.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialReport.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialReport.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _svFinancialDataInput
            // 
            this._svFinancialDataInput.Reset();
            this._svFinancialDataInput.SheetName = "Financial Data Input";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._svFinancialDataInput.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._svFinancialDataInput.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svFinancialDataInput.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _svKeyMetricSettings
            // 
            this._svKeyMetricSettings.Reset();
            this._svKeyMetricSettings.SheetName = "Key Metric Settings";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._svKeyMetricSettings.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._svKeyMetricSettings.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svKeyMetricSettings.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // _svCalculations
            // 
            this._svCalculations.Reset();
            this._svCalculations.SheetName = "Calculations";
            // Formulas and custom names must be loaded with R1C1 reference style
            this._svCalculations.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this._svCalculations.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svCalculations.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svCalculations.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svCalculations.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svCalculations.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svCalculations.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svCalculations.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this._svCalculations.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this._svCalculations.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this._svCalculations.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this._svCalculations.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // menuStrip1
            // 
            this.menuStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
            this.viewToolStripMenuItem});
            this.menuStrip1.Location = new System.Drawing.Point(5, 5);
            this.menuStrip1.Name = "menuStrip1";
            this.menuStrip1.Size = new System.Drawing.Size(147, 24);
            this.menuStrip1.TabIndex = 0;
            this.menuStrip1.Text = "menuStrip1";
            // 
            // viewToolStripMenuItem
            // 
            this.viewToolStripMenuItem.DropDownItems.AddRange(new System.Windows.Forms.ToolStripItem[] {
            this._miConditionalFormats,
            this._miNameManager});
            this.viewToolStripMenuItem.Name = "viewToolStripMenuItem";
            this.viewToolStripMenuItem.Size = new System.Drawing.Size(44, 20);
            this.viewToolStripMenuItem.Text = "View";
            // 
            // _miConditionalFormats
            // 
            this._miConditionalFormats.Name = "_miConditionalFormats";
            this._miConditionalFormats.Size = new System.Drawing.Size(182, 22);
            this._miConditionalFormats.Text = "Conditional Formats";
            // 
            // _miNameManager
            // 
            this._miNameManager.Name = "_miNameManager";
            this._miNameManager.Size = new System.Drawing.Size(182, 22);
            this._miNameManager.Text = "Name Manager";
            // 
            // nameBox
            // 
            this.nameBox.Dock = System.Windows.Forms.DockStyle.Top;
            this.nameBox.Location = new System.Drawing.Point(5, 29);
            this.nameBox.MaxLength = 255;
            this.nameBox.Name = "nameBox";
            this.nameBox.Size = new System.Drawing.Size(147, 21);
            this.nameBox.TabIndex = 1;
            // 
            // formulaTextBox1
            // 
            this.formulaTextBox1.Dock = System.Windows.Forms.DockStyle.Top;
            this.formulaTextBox1.Location = new System.Drawing.Point(5, 50);
            this.formulaTextBox1.Multiline = false;
            this.formulaTextBox1.Name = "formulaTextBox1";
            this.formulaTextBox1.Size = new System.Drawing.Size(147, 23);
            this.formulaTextBox1.TabIndex = 2;
            // 
            // AnnualFinancialReport
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "AnnualFinancialReport";
            this.pnlMain.ResumeLayout(false);
            this.pnlControls.ResumeLayout(false);
            this.pnlControls.PerformLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._svFinancialReport)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._svFinancialDataInput)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._svKeyMetricSettings)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this._svCalculations)).EndInit();
            this.menuStrip1.ResumeLayout(false);
            this.menuStrip1.PerformLayout();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView _svFinancialReport;
        private FarPoint.Win.Spread.SheetView _svFinancialDataInput;
        private FarPoint.Win.Spread.SheetView _svKeyMetricSettings;
        private FarPoint.Win.Spread.SheetView _svCalculations;
        private System.Windows.Forms.MenuStrip menuStrip1;
        private System.Windows.Forms.ToolStripMenuItem viewToolStripMenuItem;
        private System.Windows.Forms.ToolStripMenuItem _miConditionalFormats;
        private System.Windows.Forms.ToolStripMenuItem _miNameManager;
        private FarPoint.Win.Spread.FormulaTextBox formulaTextBox1;
        private FarPoint.Win.Spread.NameBox nameBox;
    }
}
